/**
 * @name         RepNET Reporting Engine Main Controller
 * @version      1.0a
 * @author       Kasun Rathnayaka <kazar55@gmail.com>
 * @author       Ramindu Deshapriya <rasade88@gmail.com>
 * @author 		 Prabodha Dissanayake <prabodha89@gmail.com>
 * @author 		 Thusira Dissanayake <thusira14@gmail.com>
 * @about        Developed by MiDi
 * @license	 http://www.gnu.org/licenses/lgpl-2.1.html GNU Lesser General Public License (LGPL)
 * @lastModified 2012.09.24
 */
package com.midi.repnet.main;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;

import com.midi.repnet.models.*;

/**
 * Class to handle reports and views
 * 
 * @author Prabodha
 * 
 */
public class Reports {

	static String sql;
	static ResultSet rs;
	static Statement statement;
	static Statement regionStatement;
	
	

	// Method that check the date cheques
	public static ArrayList<ChequeResults> getChequeListForDate(String date) {
		ArrayList<ChequeResults> resultsList = new ArrayList<ChequeResults>();
		try {
			statement = Main.dbConnection.createStatement();
			sql = "SELECT * FROM cheques c INNER JOIN shops s ON c.shop_id=s.id WHERE date='"
					+ date + "'"; // Select
			// all
			// the
			// Cheques
			// that
			// cash
			// on
			// the
			// specified
			// date

			rs = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			while (rs.next()) {
				// creating a Result object and passing values
				ChequeResults cr = new ChequeResults(rs.getString("bill_no"),
						rs.getString("cheque_no"), rs.getFloat("amount"),
						rs.getString("date"), rs.getString("bank"),
						rs.getString("status"), rs.getString("name"));

				resultsList.add(cr);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultsList;

	}

	public static boolean deleteCheque(String cheque_no,String bill_no, float amount) {
		int deleteCount = 0;
		int addedRemainder = 0;
		boolean result = false;
		//System.out.print(amount+"amount");
		try {
			
			addedRemainder = Main.dbConnection.createStatement()
					.executeUpdate("UPDATE transactions SET remainder=remainder+'"
							+ amount + "' WHERE bill_no='" + bill_no + "' ");
			
			statement= Main.dbConnection.createStatement();
			sql = "DELETE  FROM cheques WHERE cheque_no='" + cheque_no + "'"; // Select
																				// all
																				// the
																				// Cheques
																				// that
																				// cash
																				// on
																				// the
																				// specified
																				// date

			deleteCount = statement.executeUpdate(sql);

			

		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (deleteCount > 0 && addedRemainder > 0) {
			result = true;

		}
		return result;
	}
	
// Cheque Bounced updating
	public static boolean updateChequeBounce(String bill_no, float amount,String cheque_no) {
		DecimalFormat df = new DecimalFormat();
	    df.setMinimumFractionDigits(2);
	    df.setMaximumFractionDigits(2);
	    df.setGroupingUsed(false);
		boolean val = false;
		int addedRemainder = 0;

		try {
			
			addedRemainder = Main.dbConnection.createStatement()
					.executeUpdate("UPDATE transactions SET remainder=remainder+'"
							+ df.format(amount) + "' WHERE bill_no='" + bill_no + "' ");
			
			statement = Main.dbConnection.createStatement();
			sql = "UPDATE cheques SET status='Bounced' WHERE cheque_no='"
					+ cheque_no + "' AND status='ok'"; // Select all the Cheques
														// that cash on the
														// specified date

			int result = statement.executeUpdate(sql);
			if (result > 0) {
				val = true;
				// remainder calculation
			} else {
				val = false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return val;
	}

	// //////////////////////////////////////////////////////////////////////
	// Method that check the Overdue Credit
	public static ArrayList<CreditElement> getOverdueCredit(String date) {
		ArrayList<CreditElement> resultsList = new ArrayList<CreditElement>();
		try {
			statement = Main.dbConnection.createStatement();
			sql = "SELECT * FROM transactions WHERE DATEDIFF('" + date
					+ "',date)>10 AND remainder>0"; // Select all the Shops that
													// have Credit overdue
			rs = statement.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			while (rs.next()) {
				// creating a Result object and passing values

				CreditElement cr = new CreditElement(rs.getString("bill_no"),
						rs.getString("agency_id"), rs.getString("shop_id"),
						rs.getString("date"), rs.getFloat("remainder"));

				resultsList.add(cr);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultsList;

	}

	public static ArrayList<CreditElement> getCreditCollectionList(
			HashMap<String, Object> params) {
		ArrayList<CreditElement> resultsList = new ArrayList<CreditElement>();
		try {
			statement = Main.dbConnection.createStatement();
			Agency agency = (Agency) params.get("agency");

			Shop shop = (Shop) params.get("shop");
			String date = params.get("date").toString();
			String sql = "SELECT * FROM transactions "
					+ "WHERE agency_id LIKE '" + agency.getNameOrId()
					+ "' AND " + "shop_id LIKE '" + shop.getNameOrId()
					+ "' AND " + "date LIKE '" + date + "'";
			System.out.println(sql);
			ResultSet creditResult = statement.executeQuery(sql);
			if (!params.get("region").toString().equals("Choose Region")) {
				Region region = (Region) params.get("region");
				regionStatement = Main.dbConnection.createStatement();
				ResultSet shopResult = regionStatement
						.executeQuery("SELECT id FROM shops WHERE region_id='"
								+ region.getId() + "'");
				while (creditResult.next()) {
					if (creditResult.getFloat("remainder") > 0.0) {
						while (shopResult.next()) {
							if (creditResult.getString("shop_id").equals(
									shopResult.getString("id"))) {
								CreditElement cr = new CreditElement(
										creditResult.getString("bill_no"),
										creditResult.getString("agency_id"),
										creditResult.getString("shop_id"),
										creditResult.getString("date"),
										creditResult.getFloat("amount"),
										creditResult.getFloat("remainder"));
								resultsList.add(cr);
								break;
							}
						}
					}
				}
			} else {
				while (creditResult.next()) {
					if (creditResult.getFloat("remainder") > 0.0) {

						CreditElement cr = new CreditElement(
								creditResult.getString("bill_no"),
								creditResult.getString("agency_id"),
								creditResult.getString("shop_id"),
								creditResult.getString("date"),
								creditResult.getFloat("amount"),
								creditResult.getFloat("remainder"));
						resultsList.add(cr);
					}
				}
			}
			return resultsList;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static ArrayList<CreditElement> getCreditPaidList(
			HashMap<String, Object> params) {
		ArrayList<CreditElement> resultsList = new ArrayList<CreditElement>();
		String date = params.get("date").toString();
		try {
			statement = Main.dbConnection.createStatement();
			sql = "SELECT * FROM transactions WHERE date = '"+date+"'"; // Select all the Shops that
													// have Credit paid
			rs = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			while (rs.next()) {
				// creating a Result object and passing values
				if ( rs.getFloat("remainder") <= 0.0 ) {
					CreditElement cr = new CreditElement(rs.getString("bill_no"),
							rs.getString("agency_id"), rs.getString("shop_id"),
							rs.getString("date"), rs.getFloat("amount"));
	
					resultsList.add(cr);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultsList;
	}

}
